import pandas as pd
import matplotlib.pyplot as plt


def main():
    # 创建示例DataFrame
    df1 = pd.DataFrame(
        {"a": [4, 5, 6],
         "b": [7, 8, 9],
         "c": [10, 11, 12]},
        index=[1, 2, 3]
    )
    df2 = pd.DataFrame(
        {"a": [13, 14, 15],
         "b": [16, 17, 18],
         "c": [19, 20, 21]},
        index=[4, 5, 6]
    )
    # 重塑数据
    # 按列值排序
    print("按'mpg'列值升序排序:")
    print(df1.sort_values('a'))
    print("\n按'mpg'列值降序排序:")
    print(df1.sort_values('a', ascending=False))
    # 重命名列
    print("\n重命名列:")
    print(df1.rename(columns={'a': 'year'}))
    # 排序索引
    print("\n排序索引:")
    print(df1.sort_index())
    # 重置索引
    print("\n重置索引:")
    print(df1.reset_index())
    # 长格式数据转换
    print("\n长格式数据转换:")
    print(pd.melt(df1))
    # 宽格式数据转换
    df_melted = pd.melt(df1)
    print("\n宽格式数据转换:")
    print(df_melted.pivot(columns='variable', values='value'))
    # 合并DataFrame
    print("\n按行合并DataFrame:")
    print(pd.concat([df1, df2]))
    print("\n按列合并DataFrame:")
    print(pd.concat([df1, df2], axis=1))
    # 删除列
    print("\n删除列:")
    print(df1.drop(columns=['b', 'c']))

    # 子集选择
    # 选择行
    print("\n选择行(10-20行，示例这里行数不足，实际运行按数据情况):")
    print(df1.iloc[0:2])
    # 选择列
    print("\n选择列(位置1, 2):")
    print(df1.iloc[:, [1, 2]])
    # 按逻辑条件选择行
    print("\n按逻辑条件选择行(a列大于5):")
    print(df1[df1['a'] > 5])
    # 删除重复行
    print("\n删除重复行:")
    print(df1.drop_duplicates())
    # 随机选择行
    print("\n随机选择50%的行:")
    print(df1.sample(frac=0.5))
    print("\n随机选择2行:")
    print(df1.sample(n=2))
    # 选择最大和最小的n个条目
    print("\n选择最大的2个条目:")
    print(df1.nlargest(2, 'a'))
    print("\n选择最小的2个条目:")
    print(df1.nsmallest(2, 'a'))
    # 选择开头和结尾的n行
    print("\n选择开头2行:")
    print(df1.head(2))
    print("\n选择结尾2行:")
    print(df1.tail(2))
    # 选择列
    print("\n选择多列:")
    print(df1[['a', 'b']])
    print("\n选择单列:")
    print(df1['a'])
    # 按正则表达式选择列
    print("\n按正则表达式选择列(匹配'a'):")
    print(df1.filter(regex='a'))

    # 使用query()方法过滤行
    print("\n使用query()方法过滤行(a列大于5):")
    print(df1.query('a > 5'))
    print("\n使用query()方法过滤行(a列大于5且b列小于8):")
    print(df1.query('a > 5 and b < 8'))
    print("\n使用query()方法过滤行(示例中无字符串列，实际按数据情况，如匹配列名含特定字符串):")
    # 假设df1有字符串列，可使用如下代码
    # df1['str_col'] = ['abc1', 'def2', 'ghi3']
    # print(df1.query('str_col.str.startswith("abc")', engine="python"))

    # 总结数据
    print("\n统计唯一值数量:")
    print(df1['a'].value_counts())
    print("\n行数:")
    print(len(df1))
    print("\n形状:")
    print(df1.shape)
    print("\n唯一值数量:")
    print(df1['a'].nunique())
    print("\n描述性统计:")
    print(df1.describe())
    # 计算并添加新列
    print("\n计算并添加新列:")
    print(df1.assign(Area=lambda df: df['a'] * df['b']))
    # 添加单例
    df1['Volume'] = df1['a'] * df1['b'] * df1['c']
    print("\n添加单列Volume:")
    print(df1)
    # 分箱
    print("\n分箱(分3箱):")
    print(pd.qcut(df1['a'], 3, labels=False))

    # 聚合函数
    print("\n求和:")
    print(df1.sum())
    print("\n最小值:")
    print(df1.min())
    print("\n最大值:")
    print(df1.max())
    print("\n平均值:")
    print(df1.mean())
    print("\n方差:")
    print(df1.var())
    print("\n标准差:")
    print(df1.std())
    print("\n计数:")
    print(df1.count())
    print("\n中位数:")
    print(df1.median())
    print("\n分位数:")
    print(df1.quantile([0.25, 0.75]))

    # 应用函数
    def square(x):
        return x ** 2

    print("\n应用函数:")
    print(df1.apply(square))
    # 元素级操作
    print("\n元素级最大值:")
    print(df1.max(axis=1))
    print("\n元素级最小值:")
    print(df1.min(axis=1))
    print("\n绝对值:")
    print(df1.abs())
    # 裁剪值
    print("\n裁剪值:")
    print(df1.clip(lower=5, upper=10))
    # 移位操作
    print("\n值向前移动1:")
    print(df1.shift(1))
    print("\n值向后移动1:")
    print(df1.shift(-1))
    # 累积操作
    print("\n累积和:")
    print(df1.cumsum())
    print("\n累积最大值:")
    print(df1.cummax())
    print("\n累积最小值:")
    print(df1.cummin())
    print("\n累积乘积:")
    print(df1.cumprod())
    # 排名
    print("\n密集排名:")
    print(df1.rank(method='dense'))
    print("\n最小排名:")
    print(df1.rank(method='min'))
    print("\n百分比排名:")
    print(df1.rank(pct=True))
    print("\n按出现顺序排名:")
    print(df1.rank(method='first'))

    # 分组数据
    print("\n按'a'列分组:")
    print(df1.groupby(by='a').sum())
    # 创建多层索引DataFrame
    index = pd.MultiIndex.from_tuples(
        [('d', 1), ('d', 2), ('e', 2)], names=['n', 'v'])
    df_multi = pd.DataFrame(
        {"a": [4, 5, 6],
         "b": [7, 8, 9],
         "c": [10, 11, 12]},
        index=index
    )
    print("\n多层索引DataFrame:")
    print(df_multi)
    print("\n按索引层'v'分组:")
    print(df_multi.groupby(level='v').sum())

    # 合并数据集
    adf = pd.DataFrame(
        {"x1": ['A', 'B', 'C'],
         "x2": [1, 2, 3]}
    )
    bdf = pd.DataFrame(
        {"x1": ['A', 'B', 'D'],
         "x3": ['T', 'F', 'T']}
    )
    print("\n左连接:")
    print(pd.merge(adf, bdf, how='left', on='x1'))
    print("\n右连接:")
    print(pd.merge(adf, bdf, how='right', on='x1'))
    print("\n内连接:")
    print(pd.merge(adf, bdf, how='inner', on='x1'))
    print("\n外连接:")
    print(pd.merge(adf, bdf, how='outer', on='x1'))
    # 集合操作
    ydf = pd.DataFrame(
        {"x1": ['A', 'B', 'C'],
         "x2": [1, 2, 3]}
    )
    zdf = pd.DataFrame(
        {"x1": ['B', 'C', 'D'],
         "x2": [2, 3, 4]}
    )
    print("\n交集(示例中使用merge默认内连接模拟，实际可按需求调整):")
    print(pd.merge(ydf, zdf, how='inner', on='x1'))
    print("\n并集:")
    print(pd.merge(ydf, zdf, how='outer', on='x1'))
    print("\n差集(ydf中独有的行):")
    print(ydf[~ydf['x1'].isin(zdf['x1'])])
    print("\n差集(使用merge并筛选，ydf中独有的行):")
    result = pd.merge(ydf, zdf, how='outer', indicator=True)
    print(result.query('_merge == "left_only"').drop(columns=['_merge']))

    # 处理缺失数据
    df_missing = pd.DataFrame(
        {"x1": ['A', 'B', 'C', 'D'],
         "x2": [1, 2, None, 4],
         "x3": ['T', 'F', None, 'T']}
    )
    print("\n删除含缺失值的行:")
    print(df_missing.dropna())
    print("\n填充缺失值:")
    print(df_missing.fillna(0))

    # 窗口操作
    print("\n扩展窗口操作(累积和，示例数据较少，实际按数据情况):")
    print(df1.expanding().sum())
    print("\n滚动窗口操作(窗口大小为2，示例数据较少，实际按数据情况):")
    print(df1.rolling(2).sum())

    # 绘图
    # 绘制散点图
    df1.plot.scatter(x='a', y='b')
    plt.title('Scatter Plot of Column a vs Column b')
    plt.xlabel('Column a')
    plt.ylabel('Column b')
    plt.show()

    # 绘制直方图
    df1['a'].plot.hist()
    plt.title('Histogram of Column a')
    plt.xlabel('Column a')
    plt.ylabel('Frequency')
    plt.show()


if __name__ == "__main__":
    main()
